package com.enfo.zjabank.dao;

import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSON;
import com.enfo.zjabank.model.Pager;

import java.math.BigDecimal;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by zhoum on 2016/8/3.
 */
@Repository
public class ChartsDaoImpl extends BaseDao implements ChartsDao {
    @Override
    public Map<String,BigDecimal[]> totalReportData() {
        //String listSql = "select b.dept_name as area,a.count as shuliang from ((select area_code,count(1) as count from buz_escape_debts where report_state='1040' and cancel_state<>'1040' group by area_code) a left join (select dept_code,dept_name from org_dept group by dept_code) b on a.area_code=b.dept_code)";
    	String listSql = "select b.dept_name as area,a.count as count from ((select area_code,count(1) as count from buz_escape_debts where report_state=1050   group by area_code) a left join (select dept_code,dept_name from org_dept group by dept_code,dept_name) b on a.area_code=b.dept_code)";
    	List<Map<String, Object>> list = this.jdbcTemplate.queryForList(listSql.toString());
    	Map<String,BigDecimal[]> map = new HashMap<>();
    	String sql1="select count(1) from buz_escape_debts where report_state=1050 ";
    	Integer b  = this.jdbcTemplate.queryForObject(sql1, Integer.class);
    	BigDecimal[] a=new BigDecimal[3];
    	if(list.size()>0){
    	   for(int i=0;i<list.size();i++){
        	  String code=(String) list.get(i).get("area");
        	  a[0]=new BigDecimal(((Long)list.get(i).get("count")).toString());
        	  String area_code=this.jdbcTemplate.queryForObject("select dept_code from org_dept where dept_name='"+code+"'", String.class);
        	  String sql="select case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end from buz_escape_debtInfo where fdebt_id in (select debt_id from buz_escape_debts where report_state=1050 and area_code="+area_code+")";
        	  a[1]= this.jdbcTemplate.queryForObject(sql, BigDecimal.class);
        	  String sql2="select "+a[0]+"/"+b+"*100";
        	  a[2]= this.jdbcTemplate.queryForObject(sql2, BigDecimal.class);
        	  System.out.println("======22222====="+a[2]);
        	  map.put(code, a);
           }
    	}else{
    		map.put("没有数据",a);
    	}
    	System.out.println("====测试用例====="+map);
        return map;
    }

    @Override
    public String listLgShuJu(String gridPager,String sql) {

        //映射Pager对象
        Pager pager = JSON.parseObject(gridPager, Pager.class);
        String Sql="select count(1)  from buz_escape_debts where report_state=1050 ";
        BigDecimal Count=this.jdbcTemplate.queryForObject(Sql, BigDecimal.class);
        String Sql1="select case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end from buz_escape_debtInfo  where fdebt_id in (select debt_id from buz_escape_debts where report_state=1050 ) ";
        BigDecimal jineCount=this.jdbcTemplate.queryForObject(Sql1, BigDecimal.class);
        Map<String,BigDecimal[]> map = new HashMap<>();
    	map=totalReportData();
    	String ssql="";
    	for (Map.Entry<String, BigDecimal[]> entry : map.entrySet()) {
    		 System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
    		 String area_code=this.jdbcTemplate.queryForObject("select dept_code from org_dept where dept_name='"+entry.getKey()+"'", String.class);
    		 if("".equals(ssql)){
    		 ssql="select '"+area_code+"' as area_code,'"+entry.getValue()[0] +"' as count,"+entry.getValue()[1]+" as jinecount,"+entry.getValue()[0]+"/"+Count+"*100 as bili,case "+jineCount+">0 when 1 then "+entry.getValue()[1]+"/"+jineCount+"*100 else 0 end as jinebili ";
    		 }else{
    		 ssql=ssql+" union all "+"select '"+entry.getKey()+"' as area_code,'"+entry.getValue()[0] +"' as count,"+entry.getValue()[1]+" as jinecount,"+entry.getValue()[0]+"/"+Count+"*100 as bili,case "+jineCount+">0 when 1 then "+entry.getValue()[1]+"/"+jineCount+"*100 else 0 end as jinebili ";
    		 }
    	}
    	StringBuilder listSql = new StringBuilder(ssql);
        listSql.append(" limit "+pager.getStartRecord()+","+pager.getPageSize());
        String countSql = "select count(a.area_code) from ( select distinct area_code  from buz_escape_debts   where report_state=1050  ) a  ";
         Object[] params = new Object[]{};
        List<Map<String,Object>> result = this.jdbcTemplate.queryForList(listSql.toString());
        int recordCount = this.jdbcTemplate.queryForObject(countSql, Integer.class);
        //页容量
        int pageSize = pager.getPageSize();
        //页数
        pager.setPageCount(recordCount / pageSize + (recordCount % pageSize > 0 ? 1 : 0));
        //记录数
        pager.setRecordCount(recordCount);
        //记录
        pager.setExhibitDatas(result);
        //成功
        pager.setIsSuccess(true);

        String json = JSON.toJSONString(pager);

        System.out.println("====测试用例====="+json);
        return json;
    }

}
